import sqlite3
import os

# 数据库连接路径
DB_PATH = r'd:\manage1\graduation-project\manage1\manage1\manage\db.sqlite3'

def cleanup_user_data():
    try:
        conn = sqlite3.connect(DB_PATH)
        cursor = conn.cursor()
        
        print("开始清理用户数据...")
        
        # 1. 检查并修复空用户名或密码的记录
        cursor.execute('SELECT id, user_type, username, password, status FROM property_user WHERE username = "" OR password = ""')
        empty_records = cursor.fetchall()
        
        if empty_records:
            print(f"发现 {len(empty_records)} 条空用户名或密码的记录:")
            for record in empty_records:
                user_id, user_type, username, password, status = record
                print(f"  ID: {user_id}, 类型: {user_type}, 用户名: '{username}', 密码: '{password}', 状态: {status}")
                
                # 修复记录：设置默认用户名和密码
                new_username = f"user_{user_id}"
                new_password = f"default_{user_id}"
                cursor.execute(
                    'UPDATE property_user SET username = ?, password = ? WHERE id = ?',
                    (new_username, new_password, user_id)
                )
                print(f"    已修复: ID={user_id}, 新用户名='{new_username}', 新密码='{new_password}'")
        else:
            print("✓ 没有发现空用户名或密码的记录")
        
        # 2. 检查并修复用户类型格式
        cursor.execute('SELECT id, user_type FROM property_user WHERE user_type NOT IN ("管理员", "普通用户")')
        invalid_types = cursor.fetchall()
        
        if invalid_types:
            print(f"发现 {len(invalid_types)} 条用户类型不正确的记录:")
            for record in invalid_types:
                user_id, user_type = record
                # 如果包含'admin'或管理员字样，设为管理员，否则设为普通用户
                new_type = "管理员" if any(keyword in user_type.lower() for keyword in ["admin", "管理"]) else "普通用户"
                cursor.execute('UPDATE property_user SET user_type = ? WHERE id = ?', (new_type, user_id))
                print(f"    已修复: ID={user_id}, 从'{user_type}'改为'{new_type}'")
        else:
            print("✓ 所有用户类型格式正确")
        
        # 3. 检查用户名唯一性
        cursor.execute('SELECT username, COUNT(*) as count FROM property_user GROUP BY username HAVING COUNT(*) > 1')
        duplicate_users = cursor.fetchall()
        
        if duplicate_users:
            print(f"发现 {len(duplicate_users)} 个重复的用户名:")
            for username, count in duplicate_users:
                # 获取这些重复用户名的记录
                cursor.execute('SELECT id FROM property_user WHERE username = ? ORDER BY id', (username,))
                ids = [row[0] for row in cursor.fetchall()]
                
                # 只保留第一个，其他的添加后缀
                for i, user_id in enumerate(ids[1:], 1):
                    new_username = f"{username}_{i}"
                    cursor.execute('UPDATE property_user SET username = ? WHERE id = ?', (new_username, user_id))
                    print(f"    已修复: ID={user_id}, 从'{username}'改为'{new_username}'")
        else:
            print("✓ 所有用户名都是唯一的")
        
        # 提交所有更改
        conn.commit()
        print("\n数据清理完成！")
        
        # 显示清理后的所有用户记录
        cursor.execute('SELECT id, user_type, username, password, status FROM property_user ORDER BY id')
        all_users = cursor.fetchall()
        print(f"\n当前所有用户记录 ({len(all_users)} 条):")
        for user in all_users:
            print(f"  ID: {user[0]}, 类型: {user[1]}, 用户名: '{user[2]}', 密码: '{user[3]}', 状态: {user[4]}")
        
    except sqlite3.Error as e:
        print(f"数据库错误: {e}")
        if conn:
            conn.rollback()
    except Exception as e:
        print(f"发生错误: {e}")
        if conn:
            conn.rollback()
    finally:
        if conn:
            conn.close()
            print("\n数据库连接已关闭")

if __name__ == "__main__":
    cleanup_user_data()
